/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package dalobject;

import java.rmi.RemoteException;
import java.sql.Connection;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.logging.Level;
import java.util.logging.Logger;
import utils.DataRow;
import utils.DataTable;

/**
 *
 * @author Mrkupi
 */
public class DALQuerryImpl implements dalInterface.DALQuerry
{
    private final Connection conn;
    
    public  DALQuerryImpl( Connection conn )
    {
        this.conn   =   conn;
    }
    
    @Override
    public DataTable getAccountByName(String name) throws RemoteException 
    {
        synchronized (conn)
        {
            try {
                Statement st = this.conn.createStatement();
                String queryString = String.format("SELECT Name, Experience, Level, TypeID FROM ACCOUNT "
                        + " WHERE Name LIKE \'%%%s%%\'", name);

                return DataTable.createDataTable(st.executeQuery(queryString), "Name", "Experience", "Level", "TypeID");
            } catch (SQLException ex) {
                System.out.println( "Error in querry list user" );
                Logger.getLogger(DALQuerryImpl.class.getName()).log(Level.SEVERE, null, ex);
            }

            return null;
        }
    }

    @Override
    public DataTable getAllAccountTypeByTypeID(String type) throws RemoteException {
        synchronized (conn)
        {
            try {
                Statement st = this.conn.createStatement();
                String queryString = String.format("SELECT * FROM ACCOUNTTYPE WHERE Name TYPEID=\'%s\'", type);

                return DataTable.createDataTable(st.executeQuery(queryString), "TypeID", "Description");
            } catch (SQLException ex) {
                System.out.println( "Error in querry list user" );
                Logger.getLogger(DALQuerryImpl.class.getName()).log(Level.SEVERE, null, ex);
            }

            return null;
        }
    }

    @Override
    public DataTable getAllAccountType() throws RemoteException {
        synchronized (conn)
        {
            try {
                Statement st = this.conn.createStatement();
                String queryString = String.format("SELECT * FROM ACCOUNTTYPE");

                return DataTable.createDataTable(st.executeQuery(queryString), "TypeID", "Description");
            } catch (SQLException ex) {
                System.out.println( "Error in querry list user" );
                Logger.getLogger(DALQuerryImpl.class.getName()).log(Level.SEVERE, null, ex);
            }

            return null;
        }
    }

    private DataTable querryMaxRoomID()
    {
        
        synchronized (conn)
        {
            try
            {
                // querry fire
                Statement st        =   this.conn.createStatement();
                String queryString  =   String.format("select MAX( RoomID ) from ROOM");

                return DataTable.createDataTable( st.executeQuery(queryString), "MaxRoomID" );
            }catch( SQLException ex )
            {
                System.out.println( "Error in querry max room ID" );
                Logger.getLogger(DALQuerryImpl.class.getName()).log(Level.SEVERE, null, ex);
            }
            return null;
        }
    }
    
    @Override
    public boolean registerRoomChat( String name, String password, int limit ) throws RemoteException {
        
        synchronized (conn)
        {
            try {
                // get max room // bad
                int max         =   0;
                DataTable dt    =   querryMaxRoomID();
                if( dt != null )
                {
                    DataRow row =   dt.get(0);
                    max         =   Integer.parseInt(row.get("MaxRoomID"));
                }

                // querry fire
                Statement st    =   this.conn.createStatement();
                String queryString = String.format("insert into ROOM value (%d, \'%s\', \'%s\', %d)", max, name, password, limit);

                return st.execute(queryString);
            } catch (SQLException ex) {
                System.out.println( "Error in querry list user" );
                Logger.getLogger(DALQuerryImpl.class.getName()).log(Level.SEVERE, null, ex);
            }

            return false;
        }
    }

    @Override
    public DataTable getListAccountContains(String senderID, String name) throws RemoteException
    {
        synchronized (conn)
        {
            try {
                if ("".equals(name))
                {
                    name = "%";
                }

                Statement st = this.conn.createStatement();
                String queryString = String.format("SELECT a.*, CASE WHEN r.RelationID"
                        + " IS NULL THEN 'FALSE' ELSE 'TRUE' END AS IsFriend FROM ACCOUNT a "
                        + "LEFT JOIN RELATION r "
                        + "ON r.UserName1 = a.UserID or r.UserName2 = a.UserID "
                        + "WHERE LOWER(UserName) LIKE LOWER(\'%%%s%%\') and UserID != \'%s\'",
                        name, senderID);

                return DataTable.createDataTable(st.executeQuery(queryString), "UserID", "UserName", "Experience", "Level", "IsFriend");
            } catch (SQLException ex) {
                System.out.println( "Error in querry list user" );
                Logger.getLogger(DALQuerryImpl.class.getName()).log(Level.SEVERE, null, ex);
            }

            return null;
        }
    }
}
